-- Finds the first (should be only for documents) hierarchy key associated
-- with the DocumentKey or DocumentVersionKey sent in the key parameter.
-- We make sure that we do not include results from the UniformItem hierarchy
-- rooted at '.'
CREATE PROC [dbo].[asi_DocumentHierarchyKeyByKey] @key uniqueidentifier AS
BEGIN
   DECLARE @documentKey uniqueidentifier

   EXEC asi_DocumentGetLatestKey @key, @documentKey OUT

   SELECT TOP 1 HierarchyKey
     FROM Hierarchy INNER JOIN DocumentMain ON Hierarchy.UniformKey = DocumentMain.DocumentVersionKey
                                INNER JOIN HierarchyRoot ON Hierarchy.RootHierarchyKey = HierarchyRoot.RootHierarchyKey
    WHERE DocumentMain.DocumentKey = @documentKey
                  AND HierarchyRoot.HierarchyRootName <> '.'
END

GO